-- 情侣记录小程序数据库表结构
-- 注意：在 Supabase 中执行这些 SQL 语句来创建表

-- 启用 UUID 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 用户表
CREATE TABLE users (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  avatar TEXT NOT NULL,
  birthday DATE NOT NULL,
  gender VARCHAR(10) CHECK (gender IN ('male', 'female')) NOT NULL,
  phone VARCHAR(20),
  email VARCHAR(100),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 情侣档案表
CREATE TABLE couple_profiles (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user1_id UUID REFERENCES users(id) ON DELETE CASCADE,
  user2_id UUID REFERENCES users(id) ON DELETE CASCADE,
  relationship_start DATE NOT NULL,
  status VARCHAR(20) CHECK (status IN ('dating', 'engaged', 'married')) DEFAULT 'dating',
  anniversary DATE,
  story TEXT,
  cover_image TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 纪念日表
CREATE TABLE anniversaries (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  couple_id UUID REFERENCES couple_profiles(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  date DATE NOT NULL,
  type VARCHAR(20) CHECK (type IN ('love', 'birthday', 'wedding', 'custom')) NOT NULL,
  description TEXT,
  is_recurring BOOLEAN DEFAULT true,
  reminder_days INTEGER DEFAULT 7,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 相册表
CREATE TABLE albums (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  couple_id UUID REFERENCES couple_profiles(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  cover_image TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 照片表
CREATE TABLE photos (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  album_id UUID REFERENCES albums(id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  thumbnail TEXT,
  title VARCHAR(200),
  description TEXT,
  location VARCHAR(200),
  tags TEXT[] DEFAULT '{}',
  taken_at TIMESTAMP WITH TIME ZONE NOT NULL,
  uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 消息表
CREATE TABLE messages (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  couple_id UUID REFERENCES couple_profiles(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  type VARCHAR(20) CHECK (type IN ('text', 'voice', 'image', 'emoji')) DEFAULT 'text',
  sender_id UUID REFERENCES users(id) ON DELETE CASCADE,
  receiver_id UUID REFERENCES users(id) ON DELETE CASCADE,
  is_read BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 约会记录表
CREATE TABLE date_records (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  couple_id UUID REFERENCES couple_profiles(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  date TIMESTAMP WITH TIME ZONE NOT NULL,
  location VARCHAR(200) NOT NULL,
  description TEXT,
  photos TEXT[] DEFAULT '{}',
  mood INTEGER CHECK (mood >= 1 AND mood <= 5) NOT NULL,
  cost DECIMAL(10,2),
  weather VARCHAR(50),
  tags TEXT[] DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 礼物记录表
CREATE TABLE gifts (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  couple_id UUID REFERENCES couple_profiles(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  giver_id UUID REFERENCES users(id) ON DELETE CASCADE,
  receiver_id UUID REFERENCES users(id) ON DELETE CASCADE,
  occasion VARCHAR(200) NOT NULL,
  date DATE NOT NULL,
  value DECIMAL(10,2),
  photos TEXT[] DEFAULT '{}',
  significance TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 愿望清单表
CREATE TABLE wishlist (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  couple_id UUID REFERENCES couple_profiles(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  category VARCHAR(20) CHECK (category IN ('travel', 'food', 'activity', 'gift', 'other')) NOT NULL,
  priority VARCHAR(10) CHECK (priority IN ('low', 'medium', 'high')) DEFAULT 'medium',
  status VARCHAR(20) CHECK (status IN ('pending', 'in-progress', 'completed')) DEFAULT 'pending',
  target_date DATE,
  completed_date DATE,
  photos TEXT[],
  cost DECIMAL(10,2),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引以提高查询性能
CREATE INDEX idx_couple_profiles_user1 ON couple_profiles(user1_id);
CREATE INDEX idx_couple_profiles_user2 ON couple_profiles(user2_id);
CREATE INDEX idx_anniversaries_couple ON anniversaries(couple_id);
CREATE INDEX idx_albums_couple ON albums(couple_id);
CREATE INDEX idx_photos_album ON photos(album_id);
CREATE INDEX idx_messages_couple ON messages(couple_id);
CREATE INDEX idx_messages_sender ON messages(sender_id);
CREATE INDEX idx_messages_receiver ON messages(receiver_id);
CREATE INDEX idx_date_records_couple ON date_records(couple_id);
CREATE INDEX idx_gifts_couple ON gifts(couple_id);
CREATE INDEX idx_wishlist_couple ON wishlist(couple_id);

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要的表添加更新时间触发器
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_couple_profiles_updated_at BEFORE UPDATE ON couple_profiles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_albums_updated_at BEFORE UPDATE ON albums
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_wishlist_updated_at BEFORE UPDATE ON wishlist
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 评论表（关联 moments）
CREATE TABLE IF NOT EXISTS comments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  moment_id UUID REFERENCES moments(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_comments_moment ON comments(moment_id);

-- 启用行级安全性 (RLS)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE couple_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE anniversaries ENABLE ROW LEVEL SECURITY;
ALTER TABLE albums ENABLE ROW LEVEL SECURITY;
ALTER TABLE photos ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE date_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE gifts ENABLE ROW LEVEL SECURITY;
ALTER TABLE wishlist ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

-- 为所有表创建简单的 RLS 策略，允许所有操作
CREATE POLICY "Allow all operations" ON users FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON couple_profiles FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON anniversaries FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON albums FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON photos FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON messages FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON date_records FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON gifts FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON wishlist FOR ALL USING (true);
CREATE POLICY "Allow all operations" ON comments FOR ALL USING (true);
